﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;

namespace BLL
{
  public  class ClsThanhPhanHoaHocBus
    {
      public ClsThanhPhanHoaHocInfo Info = new ClsThanhPhanHoaHocInfo();
      public DataTable SelectOne()
      {
          ClsDataAccess oConnection = new ClsDataAccess();
          string strSql = @"SELECT TTCHUNG.TenGK,DuLieuMau.TenMau,ThanhPhanHoaHoc.DoPH,ThanhPhanHoaHoc.CO2TuDo,ThanhPhanHoaHoc.CO2LienHe,ThanhPhanHoaHoc.CO2XamThuc,ThanhPhanHoaHoc.HCO3,ThanhPhanHoaHoc.OH,ThanhPhanHoaHoc.CO3,
                             ThanhPhanHoaHoc.Phenol,ThanhPhanHoaHoc.DoCungCa,ThanhPhanHoaHoc.DoCungMg,ThanhPhanHoaHoc.Cl,ThanhPhanHoaHoc.SO4,ThanhPhanHoaHoc.Fe2,ThanhPhanHoaHoc.Fe3,ThanhPhanHoaHoc.F,ThanhPhanHoaHoc.H2S,                                             
                             ThanhPhanHoaHoc.HCCuaAxitSilicic,ThanhPhanHoaHoc.PO4,ThanhPhanHoaHoc.NO2,ThanhPhanHoaHoc.NO3,ThanhPhanHoaHoc.NH4,ThanhPhanHoaHoc.DO,ThanhPhanHoaHoc.COD,ThanhPhanHoaHoc.BOD,ThanhPhanHoaHoc.CN,
                             ThanhPhanHoaHoc.Cd,ThanhPhanHoaHoc.Ni,ThanhPhanHoaHoc.Cr3,ThanhPhanHoaHoc.Cr6,ThanhPhanHoaHoc.Hg,ThanhPhanHoaHoc.Pd,ThanhPhanHoaHoc.Asen,ThanhPhanHoaHoc.Be,ThanhPhanHoaHoc.Sb,ThanhPhanHoaHoc.Se,
                             ThanhPhanHoaHoc.V,ThanhPhanHoaHoc.Sn,ThanhPhanHoaHoc.Zn,ThanhPhanHoaHoc.Cu,ThanhPhanHoaHoc.Mn,ThanhPhanHoaHoc.DauMo,ThanhPhanHoaHoc.ChatTayRua,ThanhPhanHoaHoc.TongHoaChatBVTV,ThanhPhanHoaHoc.DDT    
                             FROM TTCHUNG,DuLieuMau,ThanhPhanHoaHoc
                             WHERE TTCHUNG.MaGK= DuLieuMau.MaGK
                             AND DuLieuMau.MaMau=ThanhPhanHoaHoc.MaMau 
                             AND TTCHUNG.MaGK like'%" + Info.MaGK +
                             "%'AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "%'";

          if (oConnection.ExecuteSelect(strSql))
          {
              oConnection.ODataTable.Columns.Add("STT");
              int i = 0;
              foreach (DataRow oDRow in oConnection.ODataTable.Rows)
              {
                  oDRow["STT"] = (i + 1);
                  i++;
              }
              return oConnection.ODataTable;
          }
          else return null;
      }
      public DataTable SelectAll()
      {                
          ClsDataAccess oConnection = new ClsDataAccess();
          string strSql = @"SELECT TTCHUNG.TenGK,DuLieuMau.TenMau,ThanhPhanHoaHoc.DoPH,ThanhPhanHoaHoc.CO2TuDo,ThanhPhanHoaHoc.CO2LienHe,ThanhPhanHoaHoc.CO2XamThuc,ThanhPhanHoaHoc.HCO3,ThanhPhanHoaHoc.OH,ThanhPhanHoaHoc.CO3,
                             ThanhPhanHoaHoc.Phenol,ThanhPhanHoaHoc.DoCungCa,ThanhPhanHoaHoc.DoCungMg,ThanhPhanHoaHoc.Cl,ThanhPhanHoaHoc.SO4,ThanhPhanHoaHoc.Fe2,ThanhPhanHoaHoc.Fe3,ThanhPhanHoaHoc.F,ThanhPhanHoaHoc.H2S,                                             
                             ThanhPhanHoaHoc.HCCuaAxitSilicic,ThanhPhanHoaHoc.PO4,ThanhPhanHoaHoc.NO2,ThanhPhanHoaHoc.NO3,ThanhPhanHoaHoc.NH4,ThanhPhanHoaHoc.DO,ThanhPhanHoaHoc.COD,ThanhPhanHoaHoc.BOD,ThanhPhanHoaHoc.CN,
                             ThanhPhanHoaHoc.Cd,ThanhPhanHoaHoc.Ni,ThanhPhanHoaHoc.Cr3,ThanhPhanHoaHoc.Cr6,ThanhPhanHoaHoc.Hg,ThanhPhanHoaHoc.Pd,ThanhPhanHoaHoc.Asen,ThanhPhanHoaHoc.Be,ThanhPhanHoaHoc.Sb,ThanhPhanHoaHoc.Se,
                             ThanhPhanHoaHoc.V,ThanhPhanHoaHoc.Sn,ThanhPhanHoaHoc.Zn,ThanhPhanHoaHoc.Cu,ThanhPhanHoaHoc.Mn,ThanhPhanHoaHoc.DauMo,ThanhPhanHoaHoc.ChatTayRua,ThanhPhanHoaHoc.TongHoaChatBVTV,ThanhPhanHoaHoc.DDT           
                             FROM TTCHUNG,DuLieuMau,ThanhPhanHoaHoc
                             WHERE TTCHUNG.MaGK= DuLieuMau.MaGK
                             AND DuLieuMau.MaMau=ThanhPhanHoaHoc.MaMau
                             AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "%'";
          if (oConnection.ExecuteSelect(strSql))
          {
              oConnection.ODataTable.Columns.Add("STT");
              int i = 0;
              foreach (DataRow oDRow in oConnection.ODataTable.Rows)
              {
                  oDRow["STT"] = (i + 1);
                  i++;
              }
              return oConnection.ODataTable;
          }
          else return null;
      }
      public DataTable SelectMau()
      {
          ClsDataAccess oConnection = new ClsDataAccess();
          string strSql = @"SELECT MaMau,TenMau FROM DuLieuMau, TTCHUNG WHERE TTCHUNG.MaGK=DuLieuMau.MaGK AND TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";

          if (oConnection.ExecuteSelect(strSql))
          {
              return oConnection.ODataTable;
          }
          else return null;
      }
      public DataTable SelectGK()
      {
          ClsDataAccess oConnection = new ClsDataAccess();
          string strSql = @"SELECT MaGK,TenGK FROM TTCHUNG WHRE TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";

          if (oConnection.ExecuteSelect(strSql))
          {
              return oConnection.ODataTable;
          }
          else return null;
      }
      public DataTable SelectThiNghiem()
      {
          ClsDataAccess oConnection = new ClsDataAccess();
          string strSql = @"SELECT MaThiNghiem FROM TTThiNghiemMau";

          if (oConnection.ExecuteSelect(strSql))
          {
              return oConnection.ODataTable;
          }
          else return null;
      }
      public bool Them()
      {
          string strSql = "EXECUTE InsertThanhPhanHoaHoc'" + Info.MaMau + "','" + Info.MaThiNghiem + "','" + Info.DoPH + "','" + Info.CO2TuDo + "','" + Info.CO2LienHe
                                                            + "','" + Info.CO2XamThuc + "','" + Info.HCO3 + "','" + Info.OH + "','" + Info.CO3 + "','" + Info.Phenol
                                                            + "','" + Info.DoCungCa + "','" + Info.DoCungMg + "','" + Info.Cl + "','" + Info.SO4 + "','" + Info.Fe2
                                                            + "','" + Info.Fe3 + "','" + Info.F + "','" + Info.H2S + "','" + Info.HCCuaAxitSilicic + "','" + Info.PO4
                                                            + "','" + Info.NO2 + "','" + Info.NO3 + "','" + Info.NH4 + "','" + Info.DO + "','" + Info.COD + "','" + Info.BOD
                                                            + "','" + Info.CN + "','" + Info.Cd + "','" + Info.Ni + "','" + Info.Cr3 + "','" + Info.Cr6 + "','" + Info.Hg
                                                            + "','" + Info.Pd + "','" + Info.Asen + "','" + Info.Be + "','" + Info.Sb + "','" + Info.Se + "','" + Info.V + "','" + Info.Sn
                                                            + "','" + Info.Zn + "','" + Info.Cu + "','" + Info.Mn + "','" + Info.DauMo + "','" + Info.ChatTayRua + "','" 
                                                            + Info.TongHoaChatBVTV + "','" + Info.DDT +"'";
          ClsDataAccess oConnection = new ClsDataAccess();
          return (oConnection.ExecuteStoredProcedure(strSql));
      }

      public bool Sua()
      {
          string strSql = "EXECUTE UpdateThanhPhanHoaHoc '" + Info.MaMau + "','" + Info.MaThiNghiem + "','" + Info.DoPH + "','" + Info.CO2TuDo + "','" + Info.CO2LienHe
                                                            + "','" + Info.CO2XamThuc + "','" + Info.HCO3 + "','" + Info.OH + "','" + Info.CO3 + "','" + Info.Phenol
                                                            + "','" + Info.DoCungCa + "','" + Info.DoCungMg + "','" + Info.Cl + "','" + Info.SO4 + "','" + Info.Fe2
                                                            + "','" + Info.Fe3 + "','" + Info.F + "','" + Info.H2S + "','" + Info.HCCuaAxitSilicic + "','" + Info.PO4
                                                            + "','" + Info.NO2 + "','" + Info.NO3 + "','" + Info.NH4 + "','" + Info.DO + "','" + Info.COD + "','" + Info.BOD
                                                            + "','" + Info.CN + "','" + Info.Cd + "','" + Info.Ni + "','" + Info.Cr3 + "','" + Info.Cr6 + "','" + Info.Hg
                                                            + "','" + Info.Pd + "','" + Info.Asen + "','" + Info.Be + "','" + Info.Sb + "','" + Info.Se + "','" + Info.V + "','" + Info.Sn
                                                            + "','" + Info.Zn + "','" + Info.Cu + "','" + Info.Mn + "','" + Info.DauMo + "','" + Info.ChatTayRua + "','"
                                                            + Info.TongHoaChatBVTV + "','" + Info.DDT + "'";
          ClsDataAccess oConnection = new ClsDataAccess();
          return (oConnection.ExecuteStoredProcedure(strSql));

      }

      public bool Xoa()
      {
          string strSql = "EXECUTE DeleteThanhPhanHoaHoc '" + Info.MaMau + "'";
          ClsDataAccess oConnection = new ClsDataAccess();
          return (oConnection.ExecuteStoredProcedure(strSql));

      }
    }
}
